﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class DeptInfoDAL
    {
        public List<DeptInfo> getDept(string deptName, int begin, int end, out int total)
        {
            string sql = "";
            string strWhere = " where 1=1";
            string abc = string.Format(" where 1=1 and DeptName like '%{0}%'", deptName);

            if (deptName == string.Empty)
            {
                sql = string.Format(@"select * from (select ROW_NUMBER() over (order by DeptID desc)
            rowindex,* from DeptInfo {2}) a where rowindex between {0} and {1}", begin, end, strWhere);
            }
            else
            {
                sql = string.Format(@"select * from (select ROW_NUMBER() over (order by DeptID)
            rowindex,* from DeptInfo) a {0}", abc);
            }
            DataTable dt = DBHelper.GetTable(sql);
            List<DeptInfo> list = new List<DeptInfo>();
            //将datatable转换成list
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DeptInfo info = new DeptInfo();//实例化放在for里面
                    info.DeptID = Convert.ToInt32(dt.Rows[i]["DeptID"]);
                    info.DeptName = dt.Rows[i]["DeptName"].ToString();
                    info.DeptReMark = dt.Rows[i]["DeptReMark"].ToString();
                    list.Add(info);
                }
            }
            string sqltotal = " select COUNT(*) from DeptInfo" + strWhere;
            DataTable totalDt = DBHelper.GetTable(sqltotal);
            total = Convert.ToInt32(totalDt.Rows[0][0]);
            return list;
        }
        //人员页面中下拉框绑定数据
        public List<DeptInfo> Select(string dapt)
        {
            string sql = string.Format("select DeptID, DeptName from DeptInfo");
            DataTable dt = DBHelper.GetTable(sql);
            List<DeptInfo> list = new List<DeptInfo>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DeptInfo info = new DeptInfo();
                    info.DeptID = Convert.ToInt32(dt.Rows[i]["DeptID"]);
                    info.DeptName = dt.Rows[i]["DeptName"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }

        public string Delete(int cid)
        {
            string sql = string.Format("delete from DeptInfo where DeptID={0}", cid);
            return (DBHelper.ExecutNonQuery(sql))>0?"OK":"NO";
        }

        public string Add(DeptInfo dept)
        {
            string sql = string.Format(" insert into DeptInfo(DeptName,DeptReMark) values('{0}','{1}')", dept.DeptName,dept.DeptReMark);
            int i = DBHelper.ExecutNonQuery(sql);
            return i > 0 ? "OK" : "Error";
        }

        public string Update(DeptInfo dept)
        {
            string sql = string.Format("update DeptInfo set DeptName = '{0}',DeptReMark= '{1}'  where DeptID ={2}", dept.DeptName, dept.DeptReMark, dept.DeptID);
            int i = DBHelper.ExecutNonQuery(sql);
            return i > 0 ? "OK" : "Error";
        }

    }
}
